package com.situ.day2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

public class JDBCCRUD {
	private String sql;

	/**
	 * 运用PreparedStatement  增,插入一组数据
	 */
	@Test
	public void test1() {  
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		String name = "马柳柳";
		Integer age = 22;
		String gender = "女";
		String  banji = "HTML5";
		
		try {
			connection = JDBCUtilEnd.getConnection();
			String sql = "INSERT  INTO student (NAME,age,gender,banji) VALUES (?,?,?,?);";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, age);
			preparedStatement.setString(3, gender);
			preparedStatement.setString(4, banji);
			int count = preparedStatement.executeUpdate();  //没有返回值
			System.out.println("count" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilEnd.close(connection, preparedStatement, null);
			
		}
	}
	/**
	 * 删，删除指定的id的行数
	 */
	@Test
	public void test2() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		Integer id = 21;
		try {
			connection = JDBCUtilEnd.getConnection();
			String sql = "DELETE FROM student WHERE id = ?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			int count = preparedStatement.executeUpdate();
			System.out.println("count" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilEnd.close(connection, preparedStatement, null);
		}
	}
	
	/**
	 * 改，指定要修改的id，修改名字
	 */
	@Test
	public void test3() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		Integer id = 19; //要改目标的id
		String name = "马溜溜";//要修改的名字
		
		try {
			connection = JDBCUtilEnd.getConnection();
			String sql = "UPDATE student SET name = ? WHERE id = ?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, id);
			int count = preparedStatement.executeUpdate();
			System.out.println("count" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilEnd.close(connection, preparedStatement, null);
		}
	}
	
/**
* 查询,指定名字
* 查询，加传输的结果集
*/
	@Test
	public void test4() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			connection = JDBCUtilEnd.getConnection();
			String sql = "SELECT id,NAME,age,gender,banji FROM student;";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			List<StudentJDBC> list = new ArrayList<>();
			while (resultSet.next()) {
				Integer id = resultSet.getInt("id");
		        String name = resultSet.getString("name");
		        Integer age = resultSet.getInt("age");
		        String gender = resultSet.getString("gender");
		        String banji = resultSet.getString("banji");
		        StudentJDBC studentJDBC = new StudentJDBC(id, name, age, gender, banji);
		        list.add(studentJDBC);
			}
			for (StudentJDBC studentJDBC : list) {
				System.out.println(studentJDBC);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilEnd.close(connection, statement, resultSet);
		}
		
	}
	
	
	/**
	 *   不用建Student，打印出每列
	 */
	
	@Test
	public void test5() {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			connection = JDBCUtilEnd.getConnection();
//			String sql ="SELECT id,NAME,age,gender,banji FROM student where id = ?;"; //打印id = 26的，
			String sql ="SELECT * FROM student ;"; //全部打印
			ps = connection.prepareStatement(sql);//预编译
//			ps.setObject(1, 26);                //打印id= 26的
			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt(1) +"----" + rs.getString(2) + "------" + rs.getInt(3) + "------"
						+ rs.getString(4) + "-----" + rs.getString(5));//数字代表第几列
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtilEnd.close(connection, ps, rs);
		}
	}
}
